const { Sequelize, DataTypes } = require("sequelize");

// 从环境变量中读取数据库配置
const MYSQL_USERNAME = 'root';
const MYSQL_PASSWORD = '123456';

const host = 'localhost';
const port = '3306';
const sequelize = new Sequelize("orderdish", MYSQL_USERNAME, MYSQL_PASSWORD, {
  host: host,
  port: port,
  dialect: "mysql"
});

const User = sequelize.define("User", {
  username: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  password: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  // jobNumber: {
  //   type: DataTypes.STRING,
  //   allowNull: false,
  // },
  role: {
    type: DataTypes.STRING,
    allowNull: false,
    defaultValue: 'user'
  },
  // name: {
  //   type: DataTypes.STRING,
  //   allowNull: false,
  // },
}, {
  // 禁用 timestamps
  timestamps: false
});

//菜品
const Dish = sequelize.define("Dish", {
  name: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  price: {
    type: DataTypes.STRING,
    allowNull: true,
  },
  description: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  image: {
    type: DataTypes.STRING,
    allowNull: false,
  }
}, {
  // 禁用 timestamps
  timestamps: false
});


const DateDish = sequelize.define("DateDish", {
  date: {
    type: DataTypes.DATEONLY,
    allowNull: true,
    validate: {
      isDate: true
    }
  },
  //预定当天在公司吃饭人数
  // companyNumber: {
  //   type: DataTypes.STRING,
  //   allowNull: false,
  //   defaultValue: 0
  // },
  breakfastDishes: {
    type: DataTypes.STRING,
    allowNull: true,
  },
  lunchDishes: {
    type: DataTypes.STRING,
    allowNull: true,
  },
  dinnerDishes: {
    type: DataTypes.STRING,
    allowNull: true,
  }
}, {
  // 禁用 timestamps
  timestamps: false
});

// 取消 
// const MealType = sequelize.define("MealType", {
//   type: {
//     type: DataTypes.ENUM('breakfast', 'lunch', 'dinner'),
//     allowNull: false,
//   },
// }, {
//   // 禁用 timestamps
//   timestamps: false
// });


// 直接编辑菜品字符串，不做菜品与菜品安排的关联
// const MealTypeDish = sequelize.define("MealTypeDish", {
//   // 空模型，仅作为连接表
// }, {
//   // 禁用 timestamps
//   timestamps: false
// })

const UserDateDish = sequelize.define("UserDateDish", {
  breakfast: {
    type: DataTypes.BOOLEAN,
    allowNull: false,
    defaultValue: false
  },
  lunch: {
    type: DataTypes.BOOLEAN,
    allowNull: false,
    defaultValue: false
  },
  dinner: {
    type: DataTypes.BOOLEAN,
    allowNull: false,
    defaultValue: false
  },
  // 订餐时间
  orderingDate: {
    type: DataTypes.DATEONLY,
    allowNull: false,
    validate: {
      isDate: true
    }
  }
}, {
  // 禁用 timestamps
  timestamps: false
})

const Notice = sequelize.define("Notice", {
  title: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  content: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  ifShow: {
    type: DataTypes.BOOLEAN,
    allowNull: false,
    defaultValue: false
  },
  createDate: {
    type: DataTypes.DATE,
    allowNull: false,
    validate: {
      isDate: true
    }
  }
}, {
  // 禁用 timestamps
  timestamps: false
})

const Price = sequelize.define("Price", {
  breakfast: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  lunch: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  dinner: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  breakfastTime: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  lunchTime: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  dinnerTime: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  restaurantName: {
    type: DataTypes.STRING,
    allowNull: false,
  }
}, {
  // 禁用 timestamps
  timestamps: false
})

const Advice = sequelize.define("Advice", {
  content: {
    // 最长为300
    type: DataTypes.STRING(300),
    allowNull: false,
  },
  userId: {
    type: DataTypes.INTEGER,
    allowNull: false,
  },
  date: {
    type: DataTypes.DATEONLY,
    allowNull: false,
    validate: {
      isDate: true
    }
  }
})
// DateDish 含有三个mealType,breakfast,lunch,dinner
// DateDish.hasMany(MealType, {
//   foreignKey: 'dateId',
//   sourceKey: 'id'
// });
// MealType.belongsTo(DateDish, {
//   foreignKey: 'dateId',
//   targetKey: 'id'
// });

// MealType 和 Dish 为多对多关系 ==》取消
// MealType.belongsToMany(Dish, {
//   through: MealTypeDish,
//   foreignKey: 'mealTypeId',
//   sourceKey: 'id'
// })
// Dish.belongsToMany(MealType, {
//   through: MealTypeDish,
//   foreignKey: 'dishId',
//   sourceKey: 'id'
// })

// user和DateDish为多对多关系
User.belongsToMany(DateDish, {
  through: 'UserDateDish',
  foreignKey: 'userId',
  sourceKey: 'id'
})
DateDish.belongsToMany(User, {
  through: 'UserDateDish',
  foreignKey: 'dateId',
  sourceKey: 'id'
})

UserDateDish.belongsTo(User, {
  foreignKey: 'userId'
});
User.hasMany(UserDateDish, {
  foreignKey: 'userId'
});

UserDateDish.belongsTo(DateDish, {
  foreignKey: 'dateId'
});
DateDish.hasMany(UserDateDish, {
  foreignKey: 'dateId'
});

// 用户包含多条意见
User.hasMany(Advice, {
  foreignKey: 'userId',
  sourceKey: 'id'
})
Advice.belongsTo(User, {
  foreignKey: 'userId',
  targetKey: 'id'
})

//初始化数据库
async function init() {
  await DateDish.sync({ alter: true });
  await Dish.sync({ alter: true });
  await User.sync({ alter: true });
  // await MealType.sync({ alter: true });
  // await MealTypeDish.sync({ alter: true });
  await UserDateDish.sync({ alter: true });
  await Notice.sync({ alter: true });
  await Price.sync({ alter: true });
  await Advice.sync({ alter: true });
}

// 导出初始化方法和模型
module.exports = {
  init,
  User,
  Dish,
  DateDish,
  // MealType,
  // MealTypeDish,
  UserDateDish,
  Notice,
  Price,
  Advice
};